require "app/helpers/application_helper.rb"
require "app/helpers/callcache_helper.rb"
require "config/cdr_config.rb"

class CallcacheController < ApplicationController
  layout "cdr_explorer"
  
  ##########################################################################################################
  ##
  ## 1. destination number
  ##
  ##########################################################################################################
  def dest_phone_index
    @from, @to = parse_params()
    @trunk_groups = {}
    selected_data_centers = []
    
    if params["all"] == "1"
      for dc in DATA_CENTERS
        selected_data_centers << "dimen_dest_vcbs.vcb_data_center='%s'" % dc
      end
    else
      for dc in DATA_CENTERS
        selected_data_centers << "dimen_dest_vcbs.vcb_data_center='%s'" % dc if params[dc].to_s == "1"
      end  
    end
    
    cond = "fact_dest_trunk_groups.timestamp > %s and fact_dest_trunk_groups.timestamp < %s" % [@from, @to]
    cond2 = selected_data_centers.join(" or ")
    cond += " and (%s)" % cond2 if cond2 != nil and cond2.size != 0
    
    @temp = cond # params["nyc"] #selected_data_centers.size
    
    
    
    ##########################################################
    ##
    ## Data needed: 
    ## Number of calls per identifying_number(primary key here)
    ## , data center(s)
    ## , trunk_group_name(s)
    ## , ip(s)
    ##
    ########################################################## 
    
    @trunk_groups = DimenTrunkGroupCarrier.find(:all, \
                    :select     =>["fact_dest_trunk_groups.timestamp, dimen_dest_vcb.vcb_data_center, fact_dest_trunk_groups.calls, identifying_phone_number, trunk_group_provider_name"], \
                    :include    =>[:dimen_dest_vcb, :fact_dest_trunk_groups], \
                    :conditions =>[cond], \
                    :order      =>"identifying_phone_number")
    @per_number = {} # number => timestamp-calls pair, data_center(s), trunk_group_name(s), ip(s)
    
    for d in @trunk_groups
      my_id = d.identifying_phone_number
      @per_number[my_id] = [{}, [], [], []] if !@per_number.has_key?(d)
      for f in d.fact_dest_trunk_groups
        #puts f.timestamp, @per_number[my_id][0][f.timestamp], f.calls
        @per_number[my_id][0][f.timestamp.to_s] = 0 if !@per_number[my_id][0].has_key?(f.timestamp.to_s)
        #puts "ssss" + @per_number[my_id][0][f.timestamp.to_s].to_s
        @per_number[my_id][0][f.timestamp.to_s] += f.calls
      end
      if d.dimen_dest_vcb != nil 
        if !@per_number[my_id][1].include?(d.dimen_dest_vcb.vcb_data_center)
          @per_number[my_id][1] << d.dimen_dest_vcb.vcb_data_center
        end
      end
      
      @per_number[my_id][2] << d.src_carrier                      if !@per_number[my_id][2].include?(d.src_carrier)
      @per_number[my_id][3] << d.src_ip                           if !@per_number[my_id][3].include?(d.src_ip)
      
      
      
    end
    
    
    
    
    respond_to do |format|
      format.html # index.html.erb
      format.xml  { render :xml => @trunk_groups }
    end
  end
  
  ##########################################################################################################
  ##
  ## 2. source number (US + INT)
  ##
  ##########################################################################################################
  
  def src_phone_detail
    ####################################################################
    ##
    ## Currently this action(form) is used for both us and international
    ## So, we need to input iso_country_code as a parameter
    ## 
    ##
    ####################################################################
    response.content_type = Mime::HTML
    @from, @to = parse_params()
    iso                     = params[:iso] # US (the U.S.) or KR (KOREA)
    areacode_or_countrycode = params[:id]  # 213 (LA) or 82(KOREA)
    
    
    ## for the us phone number three digits are given to find data of all the area
    
    temp = DimenSrcLocation.find(:all, \
    :include=>["fact_src_phone_numbers"], \
    :conditions => ["iso_country_code=? \
                     and identifying_phone_number like ? \
                     and fact_src_phone_numbers.timestamp >= ? \
                     and fact_src_phone_numbers.timestamp < ?" \
    , iso \
    , areacode_or_countrycode.to_s + "%" \
    , @from \
    , @to], \
    :order => ["identifying_phone_number, fact_src_phone_numbers.timestamp ASC"])
    
    # six_digit => "calls"       => {time,calls}
    #           => "src_carrier" => src_carrier
    #           => "max"         => calls (maximum)
    # I named it as per_six_digt but it is not actually per six digit
    @six_digits = {} # per six digit or per country number if it is international
    for t in temp
      key = t.identifying_phone_number # six digit for us, country code for international
      
      @six_digits[key] = {"calls"=>{}, "src_carrier"=>t.src_carrier, "max"=>0, "total"=>0} if !@six_digits.has_key?(key)
      for f in t.fact_src_phone_numbers
        @six_digits[key]["calls"][f.timestamp] = f.calls
        @six_digits[key]["total"] += f.calls
        @six_digits[key]["max"] = f.calls if  @six_digits[key]["max"] < f.calls
      end      
    end
    
    respond_to do |format|
      format.html { render :layout=>false }# index.html.erb
    end
  end
  
  
  def src_phone_index_international
    
    response.content_type = Mime::HTML
    @from, @to = parse_params()
    
    @international_count_per_country_number, @international_count_per_country_number_clean = calls_per_country_code_international(@from, @to)
    
    respond_to do |format|
      format.html # index.html.erb
      format.xml  { render :xml => @fact_src_phone_numbers }
    end
    
  end
  
  def src_phone_index
    response.content_type = Mime::HTML
    @from, @to = parse_params()
    
    @us_count_per_area = calls_per_area_us(@from, @to) 
    
    #@international_count_per_country_number, @international_count_per_country_number_clean = calls_per_country_code_international(@from, @to)
    
    respond_to do |format|
      format.html # index.html.erb
      format.xml  { render :xml => @fact_src_phone_numbers }
    end
  end
  
  def src_phone_international
    response.content_type = Mime::HTML
    @from, @to = parse_params()
    dummy, @international_count_per_country_number_clean = calls_per_country_code_international(@from, @to)
    
    respond_to do |format|
      format.html { render :layout=>false }
      format.xml  { render :xml => @international_count_per_country_number_clean }
    end
  end
  
  def src_phone_state
    response.content_type = Mime::HTML
    
    state = params[:id]
    @from, @to = parse_params()
    
    areacode = []
    for item in DimenSrcLocation.find(:all, :select=>"distinct(substring(identifying_phone_number,1,3)) as area", :conditions=>["areacode_for_us = ?", state])
      areacode << item.area
    end
    
    #########################################################
    ###
    ### Bar chart
    ###
    #########################################################    
    @hash_data = {}
    
    @one_state = FactSrcPhoneNumber.find_by_sql(["select calls, substring(d.identifying_phone_number,1,3) as identifying_phone_number, \
                                                  d.iso_country_code, areacode_for_us \
                                                  from fact_src_phone_numbers as f inner join dimen_src_locations as d on f.dimen_src_location_id=d.id \
                                                  where f.timestamp >= ? and f.timestamp < ? and d.iso_country_code = ? and areacode_for_us = ?", @from, @to, "US", state])                      
    for item in @one_state
      if !@hash_data.has_key?(item.identifying_phone_number)
        @hash_data[item.identifying_phone_number] = 0
      end
      @hash_data[item.identifying_phone_number] += item.calls
    end
    
    @per_state_group_by_src = @hash_data
    
    
    
    respond_to do |format|
      format.html { render :layout=>false }# index.html.erb
    end
    
  end
  
  def src_phone_table
    @from, @to          = parse_params()
    @us_count_per_area                      = us_count_per_area(@from, @to)
    @international_count_per_country_number = calls_per_country_code_international(@from, @to)
    
    respond_to do |format|
      format.html 
      format.xml  { render :xml => @us_count_per_area }
    end
  end
  
  ##########################################################################################################
  ##
  ## 3. destination IPs
  ##
  ##########################################################################################################
  
  def dest_ip_index
    from, to = parse_params()
    
    @fact_count_by_dest_ips = FactCountByDestIp.find(:all, \
                                                     :conditions  =>  ["timestamp>=? and timestamp<?", from, to], \
                                                     :select      =>  ["dest_ip, sum(calls) as sum_calls, max(calls) as max_calls, min(calls) as min_calls, avg(calls) as avg_calls" ], \
                                                     :group       =>  ["dest_ip"], \
                                                     :order       =>  ["dest_ip DESC"])
    
    respond_to do |format|
      format.html # index.html.erb
      format.xml  { render :xml => @fact_count_by_dest_ips }
    end    
  end
  
  def dest_ip_detail
    from, to = parse_params()
    @header = "# of calls from: %s to %s" % [Time.at(from), Time.at(to)]
    @fact_count_by_dest_ips_detail = FactCountByDestIp.find(:all, :conditions=>["dest_ip=? and timestamp>=? and timestamp<?", params[:id].gsub("_","."), from, to], :order => "timestamp ASC" )
    
  end
  
  ##########################################################################################################
  ##
  ## 4. source IPs
  ##
  ##########################################################################################################
  
  def src_ip_index
    p = params
    from, to = parse_params()
    @fact_count_by_src_ips = FactCountBySrcIp.find(:all, \
                                                   :conditions=>["timestamp>=? and timestamp<?", from, to], \
                                                   :select=>["src_ip, sum(calls) as sum_calls, max(calls) as max_calls, min(calls) as min_calls, avg(calls) as avg_calls"], \
                                                   :group=>["src_ip"], \
                                                   :order=>["src_ip DESC"])
    
    respond_to do |format|
      format.html # index.html.erb
      format.xml  { render :xml => @fact_count_by_src_ips }
    end
  end
  
  def src_ip_detail
    from, to = parse_params()
    @fact_count_by_src_ips_detail = FactCountBySrcIp.find(:all, :conditions=>["src_ip=? and timestamp between ? and ?", params[:id].gsub("_","."), from, to], :order => "timestamp ASC" )
  end
  
  
  
  #########################################################################################################
  ##
  ##
  ## Data Manipulating functions
  ##
  ##
  #########################################################################################################
  def calls_per_area_us(from, to)
=begin
     temp = FactSrcPhoneNumber.find_by_sql(["select calls, substring(d.identifying_phone_number,1,3) as areanumber, \
                                            d.iso_country_code, areacode_for_us \
                                            from fact_src_phone_numbers as f inner join dimen_src_locations as d on f.dimen_src_location_id=d.id \                   
                                            where f.timestamp >= ? and f.timestamp < ? and d.iso_country_code = ?", from, to, "US"])
=end
    
    
    
    #TODO does it improve the speed?
    temp = FactSrcPhoneNumber.find_by_sql(["select * from src_number_us where timestamp >= ? and timestamp < ?", from, to])
    #create view src_number_us as
    #select calls, substring(d.identifying_phone_number,1,3) as areanumber, d.iso_country_code, areacode_for_us 
    #from fact_src_phone_numbers as f inner join dimen_src_locations as d on f.dimen_src_location_id=d.id 
    #where d.iso_country_code = 'US';
    
    sum_per_areacode_for_us = FactSrcPhoneNumber.find_by_sql(["select sum(calls) as sum_calls, areacode_for_us from src_number_us where timestamp >= ? and timestamp < ? group by areacode_for_us", from, to])
    sum_per_areanumber      = FactSrcPhoneNumber.find_by_sql(["select sum(calls) as sum_calls, areanumber from src_number_us where timestamp >= ? and timestamp < ? group by areanumber", from, to])
    
    us_count_per_area        = {}
    
    ###############################################################################################
    ##       Structure of these array above 
    ###############################################################################################
    # The key is areacode_for_us. This is NOT number, which is US-CA or UNNKOWN something like this
    #
    # areacode_for_us => "areanumber(probably 3 digits)" => hash_data(areanumber, number of calls) (e.g. {213=>23, 805=>5657}) #total for one areanumber
    # (e.g. US-CA)    => "iso_country_code"              => string_data (must be US)
    #                 => "sum_calls"                     => integer_data(e.g. 2434)                ##total for one state
    ###############################################################################################
    s = Time.now
    
    # In the AREA of areacode.rb in config directory
    # e.g. {"559"=>["CA","Central California: Fresno (split from 209)"]} the hash looks like this
    for item in sum_per_areacode_for_us
      us_count_per_area[item.areacode_for_us] = \
      {"areanumber"       =>{}, \
          "iso_country_code"  =>"US", \
          "sum_calls"         =>item.sum_calls}     
    end
    
    
    #### sorry for the name confusion
    for item in sum_per_areanumber
      areanumber = item.areanumber
      if AREA.has_key?(areanumber.to_s)
        areacode_for_us = "US-%s" % AREA[areanumber.to_s][0]
        us_count_per_area[areacode_for_us]["areanumber"][areanumber]=item.sum_calls if us_count_per_area.has_key?(areacode_for_us)         
      end
      
      
      
    end
=begin
     for item in temp
          us_count_per_area[item.areacode_for_us] = \
          {"areanumber"       =>{}, \
          "iso_country_code"  =>item.iso_country_code, \
          "sum_calls"         =>0}           
     end
     
     for item in temp
          us_count_per_area[item.areacode_for_us]["areanumber"][item.areanumber]=0 
     end
     
     for item in temp
          us_count_per_area[item.areacode_for_us]["areanumber"][item.areanumber]  += item.calls
          us_count_per_area[item.areacode_for_us]["sum_calls"]                    +=item.calls
     end     
=end
    puts "elapsed time for for loop" + (Time.now - s).to_s
    return us_count_per_area 
    
  end
  
  def calls_per_country_code_international(from, to)
    temp = FactSrcPhoneNumber.find_by_sql(["select f.calls, d.identifying_phone_number, d.iso_country_code \
                                            from fact_src_phone_numbers as f inner join dimen_src_locations as d on f.dimen_src_location_id=d.id \
                                            where f.timestamp >= ? and f.timestamp < ? and d.iso_country_code <> 'US'", from, to])
    
    international_count_per_country_number       = {"UNNKOWN" => {"iso_country_code"=>"UNKNOWN", "sum_calls"=>0, "max"=>0}}
    international_count_per_country_number_clean = {}
    
    for item in temp
      if item.iso_country_code == "UNKNOWN"
        if item.calls != nil
          international_count_per_country_number["UNNKOWN"]["sum_calls"] += item.calls 
          international_count_per_country_number["UNNKOWN"]["max"] = item.calls if international_count_per_country_number["UNNKOWN"]["max"] < item.calls
        end
        
      else
        international_count_per_country_number[item.identifying_phone_number] = \
        {"iso_country_code"=>item.iso_country_code, "sum_calls"=>0, "max"=>0} if !international_count_per_country_number.has_key?(item.identifying_phone_number)
        international_count_per_country_number[item.identifying_phone_number]["sum_calls"]+=item.calls
        international_count_per_country_number[item.identifying_phone_number]["max"] = item.calls \
        if international_count_per_country_number[item.identifying_phone_number]["max"] < item.calls
        end
        
        if item.iso_country_code != "UNKNOWN"
          international_count_per_country_number_clean[item.identifying_phone_number] = \
          {"iso_country_code"=>item.iso_country_code, "sum_calls"=>0} if !international_count_per_country_number_clean.has_key?(item.identifying_phone_number)
          international_count_per_country_number_clean[item.identifying_phone_number]["sum_calls"]+=item.calls
        end
        
      end
      
      
      
      return international_count_per_country_number, international_count_per_country_number_clean
      
    end
    
    
  end
